import datetime
import subprocess
import random
import redis
import time
import xlwt
from time import sleep, strftime
from playwright.sync_api import sync_playwright
import pandas as pd
import numpy as np
import pymysql.cursors
from parsel import Selector


db_host = '121.4.69.146'
db_port = 3306

db = pymysql.connect(host=db_host,
                     user='root',
                     port=db_port,
                     password='xhhg@2020',
                     database='big_data')

db1 = pymysql.connect(host='112.125.25.114', port=3306,
                      user="python", password="pythonselect", database="mykhadmin")
cursor1 = db1.cursor()

cur = db.cursor()
redis_con = redis.Redis(host='121.4.69.146', password='xhhg123456',
                        port=6389, db=2, decode_responses=True)     # 连接redis数据库


def scripy(page, zhuanli_index, inst):
    # 解析申请信息
    sqh = 'ZL' + zhuanli_index
    page_soup = Selector(page.content())
    patent_state = page_soup.xpath(
        'normalize-space(//div[text()="案件状态"]/following-sibling::div[1]/text())').extract_first()
    if not patent_state:
        patent_state = ''
    patent_name = page_soup.xpath(
        'normalize-space(//div[text()="发明名称"]/following-sibling::div[1]/text())').extract_first()
    if not patent_name:
        patent_name = ''
    sq_date = page_soup.xpath(
        'normalize-space(//div[text()="申请日"]/following-sibling::div[1]/text())').extract_first()
    if not sq_date:
        sq_date = ''
    fm_class = page_soup.xpath(
        'normalize-space(//div[text()="主分类号"]/following-sibling::div[1]/text())').extract_first()
    if not fm_class:
        fm_class = ''
    agency = page_soup.xpath(
        'normalize-space(//th[text()="代理机构名称"]/../../following-sibling::tbody[1]/tr/td[1]/text())').extract_first()
    if not agency:
        agency = ''
    dlr = page_soup.xpath(
        'normalize-space(//th[text()="代理机构名称"]/../../following-sibling::tbody[1]/tr/td[2]/text())').extract_first()
    if not dlr:
        dlr = ''
    sqr = page_soup.xpath(
        'normalize-space(//p[text()="申请人"]/../following-sibling::div[1]//table/tbody/tr/td[1]/text())').extract_first()
    if not sqr:
        sqr = ''
    fmr = page_soup.xpath(
        'normalize-space(//div[text()="发明人姓名"]/following-sibling::div[1]/text())').extract_first()
    if not fmr:
        fmr = ''

    if inst == 'XHHG' and patent_name == '--':
        try:
            redis_con.sadd("协和端口查询", sqh)
        except:
            time.sleep(5)
            redis_con.sadd("协和端口查询", sqh)
        # 使用协和端口查询
        print('@@xhhg未查到相关信息，使用xh进行查询@@')
    elif inst == 'XH' and patent_name == '--':
        try:
            redis_con.sadd("外部接口查询", sqh)
        except:
            time.sleep(5)
            redis_con.sadd("外部接口查询", sqh)
        print('@@xh端口未查到相关信息，使用外部接口查询@@')
    elif inst == 'WB' and patent_name == '--':
        try:
            redis_con.sadd("全部接口未查到", sqh)
        except:
            time.sleep(5)
            redis_con.sadd("全部接口未查到", sqh)
        print('@@全部接口均未查到@@')
    if patent_name != '--' or inst == 'WB':
        sql = f"""INSERT INTO xhhg_pantent_status_0504 (application_number, case_status, fm_name,fm_class, sq_date, created_at, updated_at, agency, patent_user, first_agent, fmr, port) VALUES
        ('{sqh}', '{patent_state}', '{patent_name}', '{fm_class}', '{sq_date}', NOW(), NOW(), '{agency}', '{sqr}', '{dlr}', '{fmr}', '{inst}')"""
        print(sql)
        try:
            cur.execute(sql)
            db.commit()
        except Exception as e:
            print(e)
            time.sleep(60)
            cur.execute(sql)
            db.commit()


def select_sqh():
    sql = 'SELECT application_number FROM xhhg_pantent_status_0504'
    cur.execute(sql)
    data = cur.fetchall()
    have_already_done = set([sqh[0].replace("ZL", '') for sqh in data])
    return have_already_done


def run(i, have_already_done, port, inst):
    # 使用账号密码登录
    port_code_dict = {'XHHG': (r'42264', r'Xhhg2019@'), 'XH': (
        '12420000420007938Y', 'XHYYkyc85755457@'), 'WB': ('13283766953', 'Ws123456+++')}
    user, passwd = port_code_dict.get(inst)
    with sync_playwright() as p:
        # browser = p.chromium.launch(headless=False)
        browser = p.firefox.launch(headless=False)
        context = browser.new_context()
        context.set_default_timeout(120000)
        context.set_default_navigation_timeout(120000)
        page = context.new_page()
        page.set_default_timeout(120000)
        page.set_default_navigation_timeout(120000)
        page.goto('https://tysf.cponline.cnipa.gov.cn/am/#/user/login')
        page.wait_for_load_state('domcontentloaded')
        if inst == 'XHHG':
            page.click('xpath=//span[text()="代理机构登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="代理机构代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="代理机构代码"]', user)
            time.sleep(1)
        elif inst == 'XH':
            page.click('xpath=//span[text()="法人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="统一社会信用代码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="统一社会信用代码"]', user)
            time.sleep(1)
        elif inst == 'WB':
            page.click('xpath=//span[text()="自然人登录"]')
            time.sleep(1)
            page.click('xpath=//input[@placeholder="手机号/证件号码"]')
            time.sleep(0.5)
            page.fill('xpath=//input[@placeholder="手机号/证件号码"]', user)
            time.sleep(1)
        page.click('xpath=//input[@placeholder="请输入密码"]')
        time.sleep(0.5)
        page.fill('xpath=//input[@placeholder="请输入密码"]', passwd)
        time.sleep(0.5)
        page.click('xpath=//span[text()="登录"]')
        time.sleep(15)
        # input('%s-滑动验证登录网站：' % i)
        page.goto('https://cpquery.cponline.cnipa.gov.cn/chinesepatent/index')
        page.wait_for_load_state()
        time.sleep(3)
        if '关于中国及多国专利审查信息查询系统新旧系统双运行模式的通知' in page.content():
            page.click(
                'xpath=//span[@class="q-btn__content text-center col items-center q-anchor--skip justify-end row"]')

        time.sleep(5)
        while redis_con.scard(port):
            sqh = redis_con.spop(port).replace("ZL", '')
            print(f'@@开始查询专利号：{sqh}@@')
            if sqh not in have_already_done:      # 跳过重复的申请号，
                start_time = time.time()
                zhuanli_url = r"https://cpquery.cponline.cnipa.gov.cn/detail/index?zhuanlisqh=%s&anjianbh" % sqh
                page.goto(zhuanli_url)
                page.wait_for_load_state('networkidle')
                # time.sleep(5)
                scripy(page, sqh, inst)
                end_time = time.time()
                print(f"本次耗时: {end_time-start_time}")
                need_time = round((end_time - start_time) *
                                  redis_con.scard(port) / 60, 1)
                print(
                    f"开始抓取'{sqh}'的信息， 剩余{redis_con.scard(port)}个，大约还需要{need_time}分钟")
                print("\n")


if __name__ == '__main__':
    # port = "XH"
    port_dict = {'XHHG': '信合红谷接口查询', 'XH': '协和端口查询', 'WB': "外部接口查询"}
    inst = 'XHHG'
    port = port_dict.get(inst)
    have_already_done = select_sqh()
    # print(have_already_done)
    print('@@获取数据库已经获取到专利数据的申请号@@')
    s_time = time.time()
    # from multiprocessing.pool import Pool
    # p = Pool(4)
    # for i in range(4):
    #     p.apply_async(run, args=(i, have_already_done, port))
    # p.close()
    # p.join()
    run(0, have_already_done, port, inst)
    db.close()
    en_time = time.time()
    print(f"本次耗时: {en_time - s_time}")
